/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Objet;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.io.*; 

import java.util.StringTokenizer;

//import org.jexcelapi.*; 
//import jxl.*; 
//import jxl.write.*; 

/**
 *
 * @author zouafr
 */
public class QueryBD {
    

    /** Paramètre de connection à la base */
    
    String nomDriver = "com.mysql.jdbc.Driver";
    
    //String url="jdbc:oracle:thin:@miage03.dmiage.u-paris10.fr:1521:miage";
    //String user="zouafr"; 
    //String mdp="apprentis2012pw";
    String url="jdbc:mysql://localhost/db_service";
    String user="root"; 
    String mdp="";

    Connection cx; 
    
    
    
    public QueryBD() 
    {
        try
        {
            Class.forName(nomDriver);
            
        }
        catch(ClassNotFoundException ex)
        {
            System.out.println("La classe "+nomDriver+" n'a pu etre trouvée");
            ex.printStackTrace();
        }
        
        try
        {    
            //cx = DriverManager.getConnection(url,user,mdp);
            cx=DriverManager.getConnection(url,"root","");
        }
        catch(SQLException ex)
        {
            System.err.println("Echec de connexion a la base11");
            System.exit(1);
        }
    }
    
    
    public Connection connect() {
        try
        {
            Class.forName(nomDriver);
        }
        catch(ClassNotFoundException ex)
        {
            System.out.println("La classe "+nomDriver+" n'a pu etre trouvée");
            ex.printStackTrace();
        }
        
        try
        {    
            cx = DriverManager.getConnection(url,user,mdp);
            return cx;
        }
        catch(SQLException ex)
        {
            System.err.println("Echec de connexion a la base22");
            System.exit(1);
            
        }
        return cx;
    }
    
    
    public Hashtable listeEnseignant()
    {
        Hashtable<String,String> listEnseignant= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT harpege, password FROM Enseignant");
            
            while(rs.next())
            {
                listEnseignant.put(rs.getString("harpege"),rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des enseignants.");
            System.exit(1);
        }
        return listEnseignant;
    }
    
    public Hashtable listeSecretaire()
    {
        Hashtable<String,String> listSecretaire= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM secretaire");
            
            
            while(rs.next())
            {
                System.out.println("OKOOOOOOOOOOOOOOOOOK");
                
                listSecretaire.put(rs.getString("login"),rs.getString("password"));
                
                System.out.println(rs.getString("login")+""+rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL du secretaire.");
            System.exit(1);
        }
        return listSecretaire;
        
    }
        
    public Enseignant creerEnseignant(String login, String password)
    {
         Enseignant NewEnseignant= new Enseignant();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM ENSEIGNANT "
                       + "WHERE harpege='"+login+"'AND password='"+password+"'");
                rs.next();

                NewEnseignant.setAdresse(rs.getString("adresse"));
                NewEnseignant.setPrenom(rs.getString("prenom"));
                NewEnseignant.setHarpege(rs.getString("harpege"));
                NewEnseignant.setId_TA(rs.getInt("Id_TA"));
                NewEnseignant.setNom(rs.getString("nom"));
                NewEnseignant.setPrenom(rs.getString("prenom"));
                NewEnseignant.setNum_Prof(rs.getString("Num_Prof"));
                NewEnseignant.setMdp(rs.getString("password"));

 
                st.close();
                rs.close();

                
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL création de l'enseignant."+ex);
                System.exit(1);
        }
        return NewEnseignant;
    }
    
    public Secretaire creerSecretaire(String login, String password)
    {
         Secretaire NewSecretaire= new Secretaire();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM Secretaire"
                       + "WHERE login='"+login+"'AND password='"+password+"'");
                rs.next();

                NewSecretaire.setNom(rs.getString("nom"));
                NewSecretaire.setPrenom(rs.getString("prenom"));
                NewSecretaire.setNumAD(rs.getInt("numAD"));
                NewSecretaire.setLogin(rs.getString("login"));
                NewSecretaire.setMdp(rs.getString("password"));

                st.close();
                rs.close();
                System.out.println("ok");
                System.out.println("_");

                /*Statement st2 = cx.createStatement();
                int numAD = NewSecretaire.getNumAD();
                
                ResultSet rs2 = st2.executeQuery
                    ("Select * FROM Absence ;");

                ArrayList<Absence> lesAbsences = new ArrayList<Absence>();

                while(rs2.next()){
                
                    lesAbsences.add(new Absence(rs2.getInt("id_absence"),
                                rs2.getString("statut"),
                                rs2.getString("creneau")));
                                        

                }
                System.out.println("aie 4");
                NewSecretaire.setListAbsence(lesAbsences);
                st2.close();
                rs2.close();*/
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL création du secretaire."+ex);
                System.exit(1);
        }
        return NewSecretaire;
    }
   
    
    public ArrayList<Absence> lesAbsences()
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * FROM Absence INNER JOIN Creneau ON Absence.id_creneau = creneau.id_creneau");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(rs1.getInt("nabsence"),
                                          rs1.getInt("netudiant"),
                                          rs1.getString("id_creneau"),
                                          rs1.getString("statut"),
                                          rs1.getInt("NENSEIGNANT"),
                                          
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations de toute les absences :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
    public ArrayList<Absence> lesAbsencesEns(String id_enseignant)
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * FROM Absence INNER JOIN Creneau ON Absence.id_creneau = creneau.id_creneau "
                             + "WHERE creneau.nenseignant="+id_enseignant+" ORDER BY Module");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(rs1.getInt("nabsence"),
                                          rs1.getInt("netudiant"),
                                          rs1.getString("id_creneau"),
                                          rs1.getString("statut"),
                                          rs1.getInt("NENSEIGNANT"),
                                          
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
    public ArrayList<EC> lesECEns(String id_enseignant)
    {
        ArrayList<EC> mesEC = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select EC_SE.Apogee, EC_SE.Libelle_EC, EC_SE.H_CM_EC, EC_SE.H_TD_EC, EC_SE.H_TP_EC, EC_SE.Type_EC "
                             + "FROM EC_SE INNER JOIN Servicee ON Servicee.Apogee = EC_SE.Apogee  "
                             + "WHERE Servicee.Harpege="+id_enseignant+" ORDER BY Libelle_EC");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                          EC a = new EC(rs1.getString("Apogee"),
                                          rs1.getString("Libelle_EC"),
                                          rs1.getInt("H_CM_EC"),
                                          rs1.getInt("H_TD_EC"),       
                                          rs1.getInt("H_TP_EC"),
                                          rs1.getString("Type_EC")          
                                          );                            
                                        mesEC.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des EC :"+ex);
                System.exit(1);
            }
            return mesEC;
    }
    
    public ArrayList<EC> lesEC()
    {
        ArrayList<EC> mesEC = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * "
                             + "FROM EC_SE   "
                             + " ORDER BY Libelle_EC");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                          EC a = new EC(rs1.getString("Apogee"),
                                          rs1.getString("Libelle_EC"),
                                          rs1.getInt("HCM_EC"),
                                          rs1.getInt("HTD_EC"),       
                                          rs1.getInt("HTP_EC"),
                                          rs1.getString("Type_EC")          
                                          );                            
                                        mesEC.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
            }
            return mesEC;
    }
    
    public ArrayList<EC> AddlesEC(String Code_Apoge, String Lib_EC, Integer NB_HCM_EC, Integer NB_HTD_EC, Integer NB_HTP_EC, String TYPE_EC)
    {
        ArrayList<EC> mesEC = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" INSERT INTO EC_SE "
                             + " VALUES ('"+ Code_Apoge +"','"+ Lib_EC +"',"+ NB_HCM_EC +","+ NB_HTD_EC +","+ NB_HTP_EC +",'"+ TYPE_EC + "'" );
                //System.out.println("can do requete");
        }
        catch (SQLException ex) {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
        }
        return mesEC;
    }
    
    public ArrayList<EC> SupprimeEC(String Apogee)
    {
        ArrayList<EC> mesEC = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" DELETE FROM EC_SE "
                             + " WHERE Apogee ="+Apogee );
                
                
                //System.out.println("can do req");
            }
        catch (SQLException ex) {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
        }
        return mesEC;
    }
    
    
    public ArrayList<EC> Supprime_LigneSE(Integer Id_Ligne)
    {
        ArrayList<EC> mesLignes = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" DELETE FROM LigneSE "
                             + " WHERE Id_Ligne ="+Id_Ligne);
                //System.out.println("can do absence");
            }
        catch (SQLException ex) {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
        }
        return mesLignes;
    }
    
    public ArrayList<EC> Supprime_Service(Integer Id_S)
    {
        ArrayList<EC> mesServices = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                //On supprime d'abord toutes les lignes du service associé
                ResultSet rs1 = st1.executeQuery                  
                     (" DELETE FROM LigneSE "
                             + " WHERE Id_S ="+Id_S);
                //ON suprime ensuite le service
                rs1 = st1.executeQuery
                     (" DELETE FROM Service "
                             + " WHERE Id_S ="+Id_S);
                
                //System.out.println("can do absence");
            }
        catch (SQLException ex) {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
        }
        return mesServices;
    }
    
    public ArrayList<EC> lesSERVICES()
    {
        ArrayList<EC> mesServices = new ArrayList<EC>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * "
                             + "FROM EC_SE   "
                             + " ORDER BY Libelle_EC");
                //System.out.println("can do requete");

               /* while(rs1.next())
                {
                                          EC a = new EC(rs1.getInt("ID_S"),
                                          rs1.getString("HARPEGE"),
                                          rs1.getString("APOGEE"),
                                          rs1.getInt("ETAT_S"),       
                                          rs1.getInt("ANNEE"),   
                                          );                            
                                        mesEC.add(a);
                }*/
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
            }
            return mesServices;
    }

    

   
   public ArrayList<Absence> lesCreneaux(String id_enseignant)
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select distinct  * FROM Creneau  "
                             + "WHERE creneau.nenseignant="+id_enseignant+" ORDER BY module");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(

                                          rs1.getString("id_creneau"),
                                          rs1.getInt("NENSEIGNANT"),                                       
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des creneaux enseignants :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
    
    public String nomEnseignant(int id) {
       Connection cx
               =connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select nom from enseignant "
                   + "where Numen="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("nom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération nom impossible, id enseignant non trouvé: "+ex);
       }
       return intitule;
    }
    
    public String nomEtudiant(int id) {
       Connection cx=connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select nom from etudiant "
                   + "where Netudiant="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("nom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération nom impossible, id étudiant non trouvé: "+ex);
       }
       return intitule;
    }
    
    public String prenomEtudiant(int id) {
       Connection cx= connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select prenom from etudiant "
                   + "where Netudiant="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("prenom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération prenom impossible, id étudiant non trouvé: "+ex);
       }
       return intitule;
    }
    
    
   /*public class FichierExcel { 

 
     File fichier = new File("D:/NetBeans/fichier_excel.xls") ; 
   
        try{ 

        WritableWorkbook ww = Workbook.createWorkbook(fichier); 

        Sheet wSheet = ww.createSheet("premier", 0); 

        WritableFont arial10font = new WritableFont (WritableFont.ARIAL, 10); 
        WritableCellFormat arial10format = new WritableCellFormat(arial10font); 

        LabelCell label0 = new Label(0, 0, "nom",arial10format ); 
        LabelCell label1 = new Label(1, 0, "prenom",arial10format); 
        LabelCell label2 = new Label(0, 1, "ptit"); 
        LabelCell label3 = new Label(1, 1, "pimousse"); 
     
        wSheet.addCell(label0); 
        wSheet.addCell(label1); 
        wSheet.addCell(label2); 
        wSheet.addCell(label3); 
  
        ww.write(); 
        ww.close(); 
        }catch(IOException e){ 
        }catch(jxl.JXLException er){} 
        } 
    }*/    
    
}
